# -*- coding: utf-8 -*-

from wt.connector.db_manager.db_untils_tools import DBUntilsTools
from wt.connector.data_connect import DataConnect


class DBOracle11gManager(DataConnect, DBUntilsTools):

    def __init__(self, ):
        super().__init__()

    def define_col(self):
        """
        定义oracle插入字段类型
        :return:
        """
        col = {
            "id_val": "INT NOT NULL PRIMARY KEY,",
            "c_02_integer_col": "number(9),",
            "c_03_double_col": "number(9,2),",
            "c_04_float_col": "number(9,2),",
            "c_05_decimal_col": "number(7,2),",
            "c_06_binary_float_col": "binary_float,",
            "c_07_binary_double_col": "binary_double,",
            "c_08_date_col": "date,",
            "c_09_datetime_val": "timestamp,",
            "c_11_char_col": "char(1),",
            "c_12_nchar_col": "nchar(5),",
            "c_13_varchar2_col": "varchar2(4000)",
            "": ")"
        }
        return col

    def cdc_set_on(self, db, table):
        print(db, table)
        sql = "ALTER TABLE {}.{} ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS".format(db, table)
        return self.exec_oracle11g_sql(db, sql)

    def define_ddl(self, db, tableName):
        """
        拼接oracle ddl语句
        :param table:
        :return:
        """
        col = self.define_col()
        ddl_sql = 'create TABLE {}.{} ( \r\n'.format(db, tableName)
        for key in col:
            line = key + " " + col[key] + "\r\n"
            ddl_sql = ddl_sql + line
        return ddl_sql

    def general_col_value(self, databaseName, tableName, num):
        """
        生成 oracle dml
        :param table:
        :return:
        """
        id_val = 1
        c_02_integer_val = self.get_random(-214748, 214748)
        c_03_double_val = self.get_random_float(-500, 500)
        c_04_float_val = self.get_random_float(-500, 500)
        c_05_decimal_val = self.get_random_float(-32768, 32767)
        c_06_binary_float_val = self.get_random(-92233720368, 92233720368)
        c_07_binary_double_val = self.get_random(-92233720368, 92233720368)
        c_08_date_val = "TO_DATE('{}','yyyy-mm-dd')".format(self.get_random_date())
        c_09_datetime_val = "TO_TIMESTAMP('{}', 'YYYY-MM-DD HH24:MI:SS')".format(self.get_random_datetime())
        c_11_char_val = "'{}'".format(self.get_random_string(1))
        c_12_nchar_val = "'{}'".format(self.get_random_string(5))
        c_13_varchar2_val = "'{}'".format(self.get_random_string(10))

        max_sql = "select max(id_val) as id from {}.{}".format(databaseName, tableName)
        max_id = self.exec_oracle11g_sql(databaseName, max_sql)
        if max_id['data'][0][0] == 0 or max_id['data'][0][0] is None:
            id_val = 1 + num
        else:
            id_val = max_id['data'][0][0] + num
        sql = "insert into {}.{} (id_val, c_02_integer_col, c_03_double_col, c_04_float_col, c_05_decimal_col, c_06_binary_float_col, " \
              "c_07_binary_double_col, c_08_date_col, c_09_datetime_val, c_11_char_col," \
              "c_12_nchar_col, c_13_varchar2_col) " \
              "values ({},{},{},{},{},{},{},{},{},{},{},{})" \
              "".format(num, tableName, id_val, c_02_integer_val, c_03_double_val, c_04_float_val, c_05_decimal_val,
                        c_06_binary_float_val,
                        c_07_binary_double_val, c_08_date_val, c_09_datetime_val, c_11_char_val,
                        c_12_nchar_val, c_13_varchar2_val)
        return sql

    def generate_dml_sql(self, databaseName, tableName, num):
        """
        生成 Oracle 11g DML 插入语句的值。
        :param database: 数据库名称
        :param table: 表名称
        :param num: 生成的记录数量
        :return: tuple (SQL template, list of tuples with values)
        """
        # 获取已有记录的数量
        res_num = self.get_count(databaseName, tableName)

        sql_template = f"insert INTO {databaseName}.{tableName} (id_val, c_02_integer_col, c_03_double_col, c_04_float_col, " \
                       f"c_05_decimal_col, c_06_binary_float_col, c_07_binary_double_col, c_08_date_col, " \
                       f"c_09_datetime_val, c_11_char_col, c_12_nchar_col, c_13_varchar2_col) VALUES " \
                       f"(:1, :2, :3, :4, :5, :6, :7, TO_DATE(:8, 'YYYY-MM-DD'), TO_TIMESTAMP(:9, 'YYYY-MM-DD HH24:MI:SS'), :10, :11, :12)"

        values_list = []
        for i in range(res_num + 1, res_num + num + 1):
            values_list.append((
                i,
                self.get_random(-214748, 214748),
                self.get_random_float(-500, 500),
                self.get_random_float(-500, 500),
                self.get_random_float(-32768, 32767),
                self.get_random(-92233720368, 92233720368),
                self.get_random(-92233720368, 92233720368),
                self.get_random_date(),
                self.get_random_datetime(),
                self.get_random_string(1),
                self.get_random_string(5),
                self.get_random_string(10)
            ))

        return sql_template, values_list

    def define_dml(self, db, table, lines):
        """
        批量生成 oracle dml 语句
        :param table:
        :param lines:
        :return:
        """
        insert_sql = ""
        while lines > 0:
            tmp = self.general_col_value(db, table, lines)
            insert_sql = insert_sql + tmp + "\r\n"
            lines = lines - 1
        return insert_sql

    def create_and_insert_sql(self, db, table, num, is_recreate=None):
        """
        生成ddl、插入dml
        :param is_recreate:
        :param db:
        :param table:
        :param num:
        :return:
        """

        # 删除表/新增表
        if is_recreate:
            ddl_sql = self.define_ddl(db, table.upper())
            self.exec_oracle11g_sql(db, "drop table {}".format(table))
            self.exec_oracle11g_sql(db, ddl_sql)

        print(db, table, num, is_recreate)
        insert_sql = self.define_dml(db, table, num)
        # print(insert_sql)
        for line in insert_sql.strip().split("\r\n"):
            self.exec_oracle11g_sql(db, line)
        num = self.get_count(db, table)
        return num

    def set_primary_combkey(self, db, table):
        table = table.upper()
        self.exec_oracle11g_sql(db, "alter TABLE {}.{} DROP PRIMARY KEY".format(db, table))
        self.exec_oracle11g_sql(db, "alter TABLE {}.{} ADD CONSTRAINT PAR PRIMARY KEY (ID, C_02_INTEGER_COL)".
                                format(db, table))

    def set_cdc_table(self, db, table):
        sql = "alter TABLE {}.{} ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS".format(db, table)
        self.exec_oracle11g_sql(db, sql)

    def get_count(self, db, table):
        sql = "select COUNT(1) AS COT FROM {}.{}".format(db, table)
        res = self.exec_oracle11g_sql(db, sql)
        if res['message'] == "Success":
            res_num = res['data'][0][0]
            return res_num
        else:
            return res

    def execute_drop_table(self, db, table):
        sql = "drop TABLE {}.{}".format(db, table)
        res = self.exec_oracle11g_sql(db, sql)
        return self.set_return_massage(res)

    # def cdc_set_on(self):
    #     sql = ""

    def execute_deleted_data(self, db, table):
        sql = ("delete FROM {}.{} WHERE ID_VAL IN (SELECT ID_VAL FROM (SELECT ID_VAL FROM {}.{} "
               "ORDER BY ID_VAL DESC)WHERE ROWNUM <= 100)").format(db, table, db, table)
        res = self.exec_oracle11g_sql(db, sql)
        if res['message'] == "Success":
            return True
        else:
            return False

    def execute_update_data(self, db, table):
        # 获取列名（移除引号并大写）
        sql1 = "select column_name FROM user_tab_columns WHERE table_name = '{}'".format(table.upper())
        res = self.exec_oracle11g_sql(db, sql1)

        print(res)

        if res['message'] != "Success":
            return False

        # 获取所有列名，排除 ID_VAL
        columns = [row[0] for row in res['data'] if row[0].lower() != 'id_val']

        # 获取最近的10个ID_VAL
        get_ids_sql = ("SELECT ID_VAL FROM (SELECT ID_VAL FROM {}.{} ORDER BY ID_VAL DESC) "
                       "WHERE ROWNUM <= 10").format(db, table)

        ids_res = self.exec_oracle11g_sql(db, get_ids_sql)
        if ids_res['message'] != "Success":
            return False

        ids = [row[0] for row in ids_res['data']]

        # 遍历每个 ID_VAL，生成更新 SQL
        for id_val in ids:
            updates = []
            for col in columns:
                if col == 'C_02_INTEGER_COL':
                    updates.append("{} = {}".format(col, self.get_random(-214748, 214748)))
                elif col == 'C_03_DOUBLE_COL':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'C_04_FLOAT_COL':
                    updates.append("{} = {}".format(col, self.get_random_float(-500, 500)))
                elif col == 'C_05_DECIMAL_COL':
                    updates.append("{} = {}".format(col, self.get_random(-32768, 32767)))
                elif col == 'C_06_BINARY_FLOAT_COL':
                    updates.append("{} = {}".format(col, self.get_random(-92233720368, 92233720368)))
                elif col == 'C_07_BINARY_DOUBLE_COL':
                    updates.append("{} = {}".format(col, self.get_random(-92233720368, 92233720368)))
                elif col == 'C_08_DATE_COL':
                    updates.append("{} = TO_DATE('{}', 'YYYY-MM-DD')".format(col, self.get_random_date()))
                elif col == 'C_09_DATETIME_VAL':
                    updates.append(
                        "{} = TO_TIMESTAMP('{}', 'YYYY-MM-DD HH24:MI:SS')".format(col, self.get_random_datetime()))
                elif col == 'C_11_CHAR_COL':
                    updates.append("{} = '{}'".format(col, self.get_random_string(1)))
                elif col == 'C_12_NCHAR_COL':
                    updates.append("{} = '{}'".format(col, self.get_random_string(5)))
                elif col == 'C_13_VARCHAR2_COL':
                    updates.append("{} = '{}'".format(col, self.get_random_string(10)))

            # 生成 update SQL 语句
            update_sql = "UPDATE {}.{} SET {} WHERE ID_VAL = {}".format(db, table, ", ".join(updates), id_val)
            print(update_sql)

            # 执行更新操作
            update_res = self.exec_oracle11g_sql(db, update_sql)
            if update_res['message'] != "Success":
                return False

        return True

    def get_db_data(self, db, table):
        sql = "SELECT * FROM (SELECT * FROM {}.{} ORDER BY ID_VAL DESC) WHERE ROWNUM <= 10".format(db, table)
        res = self.exec_oracle11g_sql(db, sql)



if __name__ == '__main__':
    a = DBOracle11gManager()
    # sql = a.define_ddl("QA_SOURCE", "WLS_ORACLE_TEST")
    # print(a.exec_oracle11g_sql("QA_SOURCE", sql))
    sql_template, values_list = a.generate_dml_sql("QA_SOURCE", "WLS_ORACLE_TEST", 1000)
    # print(a.exec_oracle11g_sql("QA_SOURCE", sql_str=sql_template, values=values_list, execute_type="batch"))
    #
    single_value = (1, 197583, 283.8748936371304, 380.7470610925815, 11872.944087308933,
                    -64109712496, 11008213679, '1905-03-15', '1971-10-24 20:30:00',
                    'p', 'ZLYcs', 'PUK8kEJ633')

    single_sql = sql_template
    response_data = a.exec_oracle11g_sql("QA_SOURCE", single_sql, execute_type='batch', values=single_value)
    print(response_data)
